﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using Rison.DataAccess.Common;
using Rison.DBHelper;

namespace Rison.DataAccess.Data.Biz
{
    /// <summary>
    /// 读写第三方数据库的对象
    /// </summary>
    public sealed class DataSourceHelperDb : DataAccessBase
    {
        public DataSourceHelperDb()
            : base()
        {
            //设置第三方数据库的连接串
            base.SetTempConnectionString(DatabaseConfigInfo.Instance.TempConnectionString);
        }

        public DataSourceHelperDb(DbBaseHelper dbTransaction)
            : base(dbTransaction)
        {
            base.SetTempConnectionString(DatabaseConfigInfo.Instance.TempConnectionString);
        }

        public DataSourceHelperDb(string tempDbConnStr)
            : base()
        {
            //设置第三方数据库的连接串
            base.SetTempConnectionString(tempDbConnStr);
        }

        public DataSourceHelperDb(string tempDbConnStr, DbBaseHelper dbTransaction)
            : base(dbTransaction)
        {
            base.SetTempConnectionString(tempDbConnStr);
        }


        protected override string tableName
        {
            get { return "Biz_Infomation"; }
        }

        protected override string IdentityFieldName
        {
            get { return "InfoID"; }
        }

        protected override string OrderBy
        {
            get { return "Biz_Infomation.CreateTime desc"; }
        }


        #region "   读取部门、诉求分类、发布的信息类别   "
        /// <summary>
        /// 读取我有诉求的诉求部门
        /// </summary>
        /// <param name="condition"></param>
        /// <returns></returns>
        public DataSet GetDepartments(string condition)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select Mailbox.ID as bmid,mailboxdisplay as bmmc, Category.CategoryName as bmfl from Mailbox ");
            sql.Append("inner join CategoryMailboxRel on Mailbox.ID = CategoryMailboxRel.MailBOxID ");
            sql.Append("inner join Category on CategoryMailboxRel.CategoryID = Category.CategoryID ");
            sql.Append("where (Category.CategoryID= '5b1d83c8-18bf-40cb-a212-cb9e48f3b158' ");
            sql.Append("or Category.parentCategoryID='5b1d83c8-18bf-40cb-a212-cb9e48f3b158') ");

            if (!string.IsNullOrEmpty(condition))
                sql.AppendFormat("and {0} ", condition);

            //sql.Append("order by Category.CategoryName,mailboxdisplay");
            sql.Append("order by Category.OrderNum,CategoryMailboxRel.OrderNum");

            return ExecuteSql(sql.ToString(), "departments");
            //return ExecuteSql("select id as bmid,MailBoxDisplay as bmmc from MailBox", "departments");
            //return ExecuteSql("select id as bmid,MailBoxDisplay as bmmc from MailBox where type='部门' and status='开放'", "departments");


        }

        public string GetDepartmentCategoryRelId(string bmid)
        {
            DataRow row = base.ExecuteSqlToDataRow("select id from CategoryMailboxRel where MailBoxID='" + bmid + "'");
            if (row != null)
                return row[0].ToString();
            return string.Empty;
        }

        //诉求的分类：投诉、意见建议、咨询、办事等
        public DataSet GetSQCategory()
        {
            return ExecuteSql("select distinct MailType as SQLB from dbo.MailBoxCategoryView", "sqCategory");
        }

        //帆布的信息的分类：公告、纠风动态等
        public DataSet GetInfoCategory()
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select CategoryID,CategoryName from Category ");
            sql.Append("inner join site on site.id=Category.siteid ");
            sql.Append("where site.name='上海长宁区纠风网'" );
            sql.Append("order by ordernum");

            DataSet data = base.ExecuteSql(sql.ToString(), "category");
            sql = null; return data;
        }
        #endregion


        #region "  读取在线调查题目、保存调查结果  "
        /// <summary>
        /// 取得当前的在线调查题以及备选答案.
        /// </summary>
        /// <returns></returns>
        public DataSet GetOnlineSurvey(string conditions)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select voteid,votename,CreateTime,DeadLine from Vote ");
            if (!string.IsNullOrEmpty(conditions))
                sql.AppendFormat("where {0} ", conditions);
            sql.Append("order by CreateTime desc");

            DataSet data = base.ExecuteSql(sql.ToString(), "vote");//投票的题目

            sql = null; return data;
        }

        /// <summary>
        /// 取得当前的在线调查题以及备选答案.
        /// </summary>
        /// <returns></returns>
        public DataSet GetCurrentOnlineSurvey()
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select voteid,votename,createtime,DeadLine from Vote ");
            sql.Append("where createtime=(select max(createtime) from vote)");

            DataSet data = base.ExecuteSql(sql.ToString(), "vote");//投票的题目

            sql = new StringBuilder();
            sql.Append("select answerid,content from answer ");//备选答案
            sql.Append("where voteid=");
            sql.Append("(select voteid from Vote where createtime=(select max(createtime) from vote)) ");
            sql.Append("order by ordernum");

            base.ExecuteSql(data, sql.ToString(), "answer");

            sql = null; return data;
        }

        public DataSet GetAllOnlineSurvey()
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select voteid,votename,createtime,DeadLine from Vote order by DeadLine");

            DataSet data = base.ExecuteSql(sql.ToString(), "vote");//投票的题目

            sql = null; return data;
        }

        public DataSet GetAllAnswerbyID(string id)
        {
            StringBuilder sql = new StringBuilder(); //order by a.VoteCount desc
            sql.AppendFormat("select v.VoteName,a.Content as AnswerContent,a.VoteCount from Answer a, vote v where a.VoteID=v.VoteID and v.VoteID='{0}' order by a.ordernum", id);

            DataSet data = base.ExecuteSql(sql.ToString(), "vote");//投票的题目

            sql = null; return data;
        }
        

        public int UpdateOnlineSurveyResult(string answerid, string ip, string browser)
        {
            StringBuilder sql = new StringBuilder();
            sql.AppendFormat("update Answer set VoteCount=VoteCount+1 where answerid='{0}'", answerid);
            int reVal = base.ExecuteNonSql(sql.ToString());

            if (reVal == 1)
            {
                sql = new StringBuilder();
                sql.Append("insert into Voter (ID,AnswerID,VoterIP,VoterBroswer,VoteTime) values ");
                sql.AppendFormat("(newid(),'{0}','{1}','{2}',getdate())", answerid, ip, browser);

                reVal = base.ExecuteNonSql(sql.ToString());
            }

            sql = null; return reVal;
        }
        #endregion



        #region "    保存我的诉求    "
        public int SaveAppeal(string sqlString)
        {
            return base.ExecuteNonSql(sqlString);            
        }

        public int SaveAppealAttachment(string sqlString)
        {
            return base.ExecuteNonSql(sqlString);
        }

        public bool PasswordExists(string password)
        {
            DataRow row = base.ExecuteSqlToDataRow("select 1 from Request where Password='" + password + "'");

            return !(row == null);
        }

        public DataRow GetsuqiuByPwd(string password)
        {
            DataRow row = base.ExecuteSqlToDataRow("select top 1 id  from Request where Password='" + password + "'");

            return row;
        }


        public int GetSXH()
        {
            DataRow row = ExecuteSqlToDataRow("select COUNT(1) from request where CAST(convert(varchar(10),GETDATE(),120) as datetime)=CAST(convert(varchar(10),SubmitTime,120) as datetime) ");
            if (row != null)
            {
                return Convert.ToInt32(row[0]) + 1;
            }
            return 1;
        }
        #endregion




        public DataSet GetTopNumInfos(int categoryId, int topNum)
        {
            return GetTopNumInfos(categoryId, topNum, 0);
        }

        public DataSet GetTopNumInfos(int categoryId, int topNum, int subjectLength)
        {
            return null;
            //StringBuilder sql = new StringBuilder();
            //sql.AppendFormat("select top {0} infoId,moduleid,imgUrl,createdate,subject as wholeSubject", topNum.ToString());
            //if (subjectLength > 1)
            //    sql.AppendFormat(",subject=case when (datalength(subject)>{0}*2) then dbo.fn_substringSubject(subject,{1}) else subject end ", (subjectLength).ToString(), (subjectLength - 1).ToString());
            //else
            //    sql.Append(",subject");
            //sql.Append(",right(convert(varchar(10),createdate,120),5) as cdate ");
            //sql.AppendFormat("from Biz_Infomation where moduleId={0} ", categoryId.ToString());
            //sql.Append("order by Biz_Infomation.CreateDate desc");

            //return base.ExecuteSql(sql.ToString(), "GetTopNumInfos");
        }

    }
}
